﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;

namespace FYPHP.Reports
{
    public partial class ProductsReport : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["fyphp"].ConnectionString);

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                SqlCommand cmdReport;
                SqlDataReader dtrReport;
                conn.Open();

                cmdReport = new SqlCommand("SELECT p_id, p_name, p_quantity, p_price, p_views, CONVERT(varchar, p_date, 103) AS pdate, p_status, username, cat_name FROM Products p, Users u, Category c WHERE p.u_id=u.u_id AND c.cat_id=p_category ORDER BY p_name", conn);
                dtrReport = cmdReport.ExecuteReader();

                ItemList.DataSource = dtrReport;
                ItemList.DataBind();

                dtrReport.Close();
                conn.Close();
            }
        }

        protected void btnSort_Click(object sender, EventArgs e)
        {
            SqlCommand cmdReport;
            SqlDataReader dtrReport;
            conn.Open();

            String sort = "";

            if (CheckBox1.Checked)
                sort = "DESC";

            if (TextBox1.Text != "" && TextBox3.Text != "")
            {
                cmdReport = new SqlCommand("SELECT p_id, p_name, p_quantity, p_price, p_views, p_status, CONVERT(varchar, p_date, 103) AS pdate, username, cat_name FROM Products p, Users u, Category c WHERE p.u_id=u.u_id AND c.cat_id=p_category AND CONVERT(DATE, p_date, 103) BETWEEN CONVERT(DATE, @fdate, 103) AND CONVERT(DATE, @tdate, 103) ORDER BY " + DropDownList1.SelectedValue + " " + sort, conn);
                cmdReport.Parameters.AddWithValue("@fdate", TextBox1.Text);
                cmdReport.Parameters.AddWithValue("@tdate", TextBox3.Text);
            }
            else if (TextBox1.Text != "" && TextBox3.Text == "")
            {
                cmdReport = new SqlCommand("SELECT p_id, p_name, p_quantity, p_price, p_views, p_status, CONVERT(varchar, p_date, 103) AS pdate, username, cat_name FROM Products p, Users u, Category c WHERE p.u_id=u.u_id AND c.cat_id=p_category AND CONVERT(DATE, p_date, 103) >= CONVERT(DATE, @fdate, 103) ORDER BY " + DropDownList1.SelectedValue + " " + sort, conn);
                cmdReport.Parameters.AddWithValue("@fdate", TextBox1.Text);
            }
            else if (TextBox1.Text == "" && TextBox3.Text != "")
            {
                cmdReport = new SqlCommand("SELECT p_id, p_name, p_quantity, p_price, p_views, p_status, CONVERT(varchar, p_date, 103) AS pdate, username, cat_name FROM Products p, Users u, Category c WHERE p.u_id=u.u_id AND c.cat_id=p_category AND CONVERT(DATE, p_date, 103) <= CONVERT(DATE, @tdate, 103) ORDER BY " + DropDownList1.SelectedValue + " " + sort, conn);
                cmdReport.Parameters.AddWithValue("@tdate", TextBox3.Text);
            }
            else
                cmdReport = new SqlCommand("SELECT p_id, p_name, p_quantity, p_price, p_views, p_status, CONVERT(varchar, p_date, 103) AS pdate, username, cat_name FROM Products p, Users u, Category c WHERE p.u_id=u.u_id AND c.cat_id=p_category ORDER BY " + DropDownList1.SelectedValue + " " + sort, conn);
 
            dtrReport = cmdReport.ExecuteReader();

            ItemList.DataSource = dtrReport;
            ItemList.DataBind();

            dtrReport.Close();
            conn.Close();
        }
    }
}